package com.example.spinning.service;

import com.example.spinning.config.SystemConfig;
import com.example.spinning.mapper.ExcelDownloadRepository;
import com.example.spinning.utils.toStringUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Service
@Transactional
public class ExcelDownloadService {

    //注入数据访问层
    @Autowired
    private ExcelDownloadRepository exceldownloadrepository;

    //获取配置文件中对应属性值
    private String getProperties;

    toStringUtil tsu = new toStringUtil();

    /**
     * @param response
     * @param properName 属性文件中的表名
     */
    @Transactional
    public void ExcelDownload(HttpServletResponse response, String properName) {

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(properName);

        //在属性文件的配置信息里获取要查询的表的字段值
        getProperties = SystemConfig.getProperty("cqwf2." + properName);
        String[] info = getProperties.split(",");

        List<String> keysList = new ArrayList<String>();            //保存英文字段值
        List<String> valuesList = new ArrayList<String>();        //保存中文字段值
        List<Map<String, Object>> tableInfoList = new ArrayList<Map<String, Object>>();    //保存数据库中查出来的所有数据

        for (int i = 0; i < info.length; i++) {
            String[] pros = info[i].split(":");
            keysList.add(pros[0]);
            valuesList.add(pros[0]);
        }
        //设置导出的文件的名字
        String filename = properName + ".xls";
        //创建表头
        HSSFRow tablerow = sheet.createRow(0);
        //循环添加表头数据
        for (int i = 0; i < keysList.size(); i++) {
            HSSFCell cell = tablerow.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(valuesList.get(i));
            cell.setCellValue(text);
        }
        //循环获取数据库中每行数据
        tableInfoList = exceldownloadrepository.selectInfo(properName, tsu.ListToString(keysList));

        for (int i = 0; i < tableInfoList.size(); i++) {

            // 一个List对象是一个Map，一行数据，一个Map对象对应一行里的一条数据
            Map tableMap = tableInfoList.get(i);

            //表头是第0行，所以从第一行开始创建
            Row row = sheet.createRow(i + 1);

            //循环创建单元格
            for (int j = 0; j < keysList.size(); j++) {
                //获取指定字段的值，判断是否为空
                Object object = tableMap.get(keysList.get(j));
                String val = "";
                if (object != null) {
                    val = object.toString();
                }
                row.createCell(j).setCellValue(val);
            }
        }
        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" + filename);
        try {
            response.flushBuffer();
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
